package com.example.demo2.student;

import com.example.demo2.Util.DBHelper;
import com.example.demo2.pojo.student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImo {
    Connection conn = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    DBHelper dbHelper = new DBHelper();
    public void insertStudent(student student) {
         dbHelper = new DBHelper();
         conn = dbHelper.getConn();
        try {
            preparedStatement = conn.prepareStatement("insert into webdesign.student(name, gender, age, class, major) VALUE (?,?,?,?,?)");
            preparedStatement.setString(1,student.getName());
            preparedStatement.setString(2,student.getGender());
            preparedStatement.setInt(3,student.getAge());
            preparedStatement.setString(4,student.getClass1());
            preparedStatement.setString(5,student.getMajor());
            int i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            dbHelper.closeAll(conn,preparedStatement,resultSet);
        }


    }

    public List<student>selectAllStudent() {
        List<student> list = new ArrayList<>();
        dbHelper = new DBHelper();
        conn = dbHelper.getConn();
        try {
            preparedStatement = conn.prepareStatement("select *from webdesign.student");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                student student = new student();
                student.setSid(resultSet.getInt("sid"));
                student.setName(resultSet.getString("name"));
                student.setGender(resultSet.getString("gender"));
                student.setAge(resultSet.getInt("age"));
                student.setClass1(resultSet.getString("class"));
                student.setMajor(resultSet.getString("major"));
                list.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            dbHelper.closeAll(conn,preparedStatement,resultSet);
        }
        return list;

    }

    public void deleteStudent(int id) {
        dbHelper = new DBHelper();
        conn = dbHelper.getConn();
        try {
            preparedStatement = conn.prepareStatement("delete  from webdesign.student where sid= ?");
            preparedStatement.setInt(1,id);
            int i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            dbHelper.closeAll(conn,preparedStatement,resultSet);
        }
    }

    public List<student> selectStudentById(int id) {
        List<student> list = new ArrayList<>();
        dbHelper = new DBHelper();
        conn = dbHelper.getConn();
        try {
            preparedStatement = conn.prepareStatement("select *from webdesign.student where sid= ?");
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                student student = new student();
                student.setSid(resultSet.getInt("sid"));
                student.setName(resultSet.getString("name"));
                student.setGender(resultSet.getString("gender"));
                student.setAge(resultSet.getInt("age"));
                student.setClass1(resultSet.getString("class"));
                student.setMajor(resultSet.getString("major"));
                list.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            dbHelper.closeAll(conn,preparedStatement,resultSet);
        }
        return list;
    }

    public void updateStudent(student student) {
        dbHelper = new DBHelper();
        conn = dbHelper.getConn();
        try {
            preparedStatement = conn.prepareStatement("update webdesign.student set name=?,gender=?,age=?,class=?,major=?");
            preparedStatement.setString(1,student.getName());
            preparedStatement.setString(2,student.getGender());
            preparedStatement.setInt(3,student.getAge());
            preparedStatement.setString(4,student.getClass1());
            preparedStatement.setString(5,student.getMajor());
            int i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            dbHelper.closeAll(conn,preparedStatement,resultSet);
        }
    }

    public void insertcourse(student student) {
        dbHelper = new DBHelper();
        conn = dbHelper.getConn();
        try {
            PreparedStatement preparedStatement1 = conn.prepareStatement("update webdesign.student set majorid=(select id from webdesign.major where major.name=?) where name=?");

            preparedStatement1.setString(1,student.getMajor());
            preparedStatement1.setString(2,student.getName());
            int i = preparedStatement1.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            dbHelper.closeAll(conn,preparedStatement,resultSet);
        }
    }
}

